{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import random\n",
    "import string"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 练习1\n",
    "创建 university 数据库 \\\n",
    "创建students表  \\\n",
    "  至少包括 ID，姓名，性别，年龄等必要字段，并自己为这些字段选择合适的字段类型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 数据库文件是university.db，不存在，则自动创建\n",
    "with sqlite3.connect('university.db') as db:\n",
    "# 创建一个cursor：\n",
    "    cursor = db.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 执行一条SQL语句：创建students表\n",
    "cursor.execute('create table if not exists students( \\\n",
    "    id integer primary key, \\\n",
    "    name text not null, \\\n",
    "    gender text not null, \\\n",
    "    age intger not null, \\\n",
    "    major text not null)')\n",
    "db.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 练习2\n",
    "增加和更新students 表的数据 \\\n",
    "至少有3名同学（周边）\n",
    "\n",
    "在university 数据库中建立class表 \\\n",
    "增加和更新表的数据\n",
    "\n",
    "至少有三门以上课程   \\\n",
    "提示，至少包括课号，课程名，教师。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "output_type": "error",
     "ename": "IntegrityError",
     "evalue": "UNIQUE constraint failed: students.id",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mIntegrityError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-4-ab64c85e7e34>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      8\u001b[0m     \u001b[0mage\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mrandom\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrandint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;36m12\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m24\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      9\u001b[0m     \u001b[0mmajor\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mmajors\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mrandom\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrandint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m3\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 10\u001b[0;31m     cursor.execute('insert into students (id, name,gender,age,major) \\\n\u001b[0m\u001b[1;32m     11\u001b[0m         values ({},\"{}\",\"{}\",{},\"{}\")'.format(i+1, name, gender, age, major))\n\u001b[1;32m     12\u001b[0m \u001b[0mdb\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcommit\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mIntegrityError\u001b[0m: UNIQUE constraint failed: students.id"
     ]
    }
   ],
   "source": [
    "# 插入数据：\n",
    "genders = ['male', 'female']\n",
    "majors = ['Math', 'CS', 'Finance', 'Economics']\n",
    "\n",
    "for i in range(20):\n",
    "    name = ''.join(random.sample(string.ascii_lowercase, 5))\n",
    "    gender = genders[random.randint(0, 1)]\n",
    "    age = random.randint(12, 24)\n",
    "    major = majors[random.randint(0, 3)]\n",
    "    cursor.execute('insert into students (id, name,gender,age,major) \\\n",
    "        values ({},\"{}\",\"{}\",{},\"{}\")'.format(i+1, name, gender, age, major))\n",
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "---------------students--------------------\n(1, 'kinqp', 'female', 24, 'Economics')\n(2, 'kbdsr', 'female', 18, 'Finance')\n(3, 'vtjxo', 'female', 12, 'Economics')\n(4, 'gnvcs', 'male', 17, 'Economics')\n(5, 'wcmvy', 'female', 13, 'Finance')\n(6, 'yuhmv', 'female', 12, 'CS')\n(7, 'gtoyh', 'female', 24, 'Economics')\n(8, 'trdvb', 'female', 24, 'Math')\n(9, 'nwqxo', 'male', 13, 'Economics')\n(10, 'ugkqn', 'male', 13, 'CS')\n(11, 'lpcqm', 'male', 13, 'Finance')\n(12, 'kgxfn', 'male', 24, 'Math')\n(13, 'vetjq', 'male', 20, 'Math')\n(14, 'nfwum', 'male', 19, 'CS')\n(15, 'mcbfi', 'female', 15, 'CS')\n(16, 'vjskh', 'male', 20, 'Math')\n(17, 'dzjra', 'female', 16, 'Economics')\n(18, 'qdvxt', 'male', 24, 'CS')\n(19, 'hnyie', 'female', 15, 'Finance')\n(20, 'iaoxv', 'male', 14, 'CS')\n"
     ]
    }
   ],
   "source": [
    "cursor.execute('select * from students')\n",
    "print('---------------students--------------------')\n",
    "for x in cursor.fetchall():\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 执行一条SQL语句：创建class表\n",
    "cursor.execute('create table if not exists class( \\\n",
    "    class_id integer primary key, \\\n",
    "    class_name text, \\\n",
    "    lecture text, \\\n",
    "    credit integer )')\n",
    "\n",
    "classes = ['Python', 'Java', 'C++', 'C', 'R', 'Go']\n",
    "lectures = ['Adam', 'Bob', 'Cyrus', 'Dan', 'Eric', 'Frank']\n",
    "Credits = [3, 2, 3, 2, 1, 1]\n",
    "for i in range(6):\n",
    "    cursor.execute('insert into class (class_id, class_name,lecture,credit) \\\n",
    "        values ({},\"{}\",\"{}\",{})'.format(i+1, classes[i], lectures[i], Credits[i]))\n",
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "---------------class--------------------\n(1, 'Python', 'Adam', 3)\n(2, 'Java', 'Bob', 2)\n(3, 'C++', 'Cyrus', 3)\n(4, 'C', 'Dan', 2)\n(5, 'R', 'Eric', 1)\n(6, 'Go', 'Frank', 1)\n"
     ]
    }
   ],
   "source": [
    "cursor.execute('select * from class')\n",
    "print('---------------class--------------------')\n",
    "for x in cursor.fetchall():\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 练习3\n",
    "在university 数据库中创建选课表enrolled表;\\\n",
    "至少包括：学生ID，选课的课号，考试分数   \\\n",
    "增加和更新表的数据\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 执行一条SQL语句：创建enrolled表\n",
    "cursor.execute('create table if not exists enrolled( \\\n",
    "    student_id integer,  \\\n",
    "    class_id integer, \\\n",
    "    credit integer, \\\n",
    "    score integer, \\\n",
    "    primary key(student_id,class_id) )')\n",
    "\n",
    "for i in range(20):\n",
    "    student_id = i+1\n",
    "    for j in range(random.randint(1, 6)):\n",
    "        class_id = j+1\n",
    "        credit = Credits[j]\n",
    "        score = random.randint(0, 100)\n",
    "        cursor.execute('insert into enrolled (student_id,class_id, credit,score) \\\n",
    "            values ({},{},{},{})'.format(student_id, class_id, credit, score))\n",
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "---------------students--------------------\n(1, 1, 3, 47)\n(1, 2, 2, 87)\n(1, 3, 3, 25)\n(1, 4, 2, 75)\n(2, 1, 3, 7)\n(2, 2, 2, 28)\n(2, 3, 3, 82)\n(2, 4, 2, 59)\n(2, 5, 1, 21)\n(2, 6, 1, 19)\n(3, 1, 3, 38)\n(3, 2, 2, 38)\n(3, 3, 3, 46)\n(3, 4, 2, 2)\n(4, 1, 3, 29)\n(4, 2, 2, 20)\n(4, 3, 3, 16)\n(4, 4, 2, 33)\n(4, 5, 1, 76)\n(4, 6, 1, 83)\n(5, 1, 3, 48)\n(5, 2, 2, 97)\n(5, 3, 3, 46)\n(5, 4, 2, 14)\n(5, 5, 1, 77)\n(5, 6, 1, 75)\n(6, 1, 3, 96)\n(6, 2, 2, 70)\n(6, 3, 3, 19)\n(7, 1, 3, 72)\n(7, 2, 2, 76)\n(8, 1, 3, 40)\n(8, 2, 2, 9)\n(8, 3, 3, 92)\n(8, 4, 2, 40)\n(9, 1, 3, 77)\n(9, 2, 2, 96)\n(10, 1, 3, 29)\n(10, 2, 2, 74)\n(10, 3, 3, 4)\n(10, 4, 2, 81)\n(10, 5, 1, 40)\n(10, 6, 1, 72)\n(11, 1, 3, 12)\n(11, 2, 2, 94)\n(11, 3, 3, 76)\n(11, 4, 2, 4)\n(12, 1, 3, 46)\n(12, 2, 2, 26)\n(13, 1, 3, 26)\n(13, 2, 2, 31)\n(13, 3, 3, 71)\n(13, 4, 2, 1)\n(13, 5, 1, 25)\n(13, 6, 1, 94)\n(14, 1, 3, 29)\n(14, 2, 2, 1)\n(14, 3, 3, 21)\n(14, 4, 2, 75)\n(14, 5, 1, 23)\n(15, 1, 3, 8)\n(15, 2, 2, 29)\n(16, 1, 3, 100)\n(16, 2, 2, 74)\n(16, 3, 3, 30)\n(16, 4, 2, 55)\n(16, 5, 1, 43)\n(16, 6, 1, 83)\n(17, 1, 3, 71)\n(17, 2, 2, 98)\n(17, 3, 3, 55)\n(18, 1, 3, 69)\n(18, 2, 2, 38)\n(18, 3, 3, 67)\n(18, 4, 2, 29)\n(18, 5, 1, 23)\n(18, 6, 1, 96)\n(19, 1, 3, 69)\n(19, 2, 2, 57)\n(19, 3, 3, 41)\n(19, 4, 2, 90)\n(20, 1, 3, 16)\n(20, 2, 2, 82)\n(20, 3, 3, 77)\n(20, 4, 2, 63)\n"
     ]
    }
   ],
   "source": [
    "cursor.execute('select * from enrolled')\n",
    "print('---------------students--------------------')\n",
    "for x in cursor.fetchall():\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 练习4\n",
    "\n",
    "使用 INNER JOIN 查询学生的个人信息和每个课程的分数。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "---------------inner join--------------------\n(1, 'kinqp', 'female', 24, 'Economics', 'Python', 3, 47)\n(1, 'kinqp', 'female', 24, 'Economics', 'Java', 2, 87)\n(1, 'kinqp', 'female', 24, 'Economics', 'C++', 3, 25)\n(1, 'kinqp', 'female', 24, 'Economics', 'C', 2, 75)\n(2, 'kbdsr', 'female', 18, 'Finance', 'Python', 3, 7)\n(2, 'kbdsr', 'female', 18, 'Finance', 'Java', 2, 28)\n(2, 'kbdsr', 'female', 18, 'Finance', 'C++', 3, 82)\n(2, 'kbdsr', 'female', 18, 'Finance', 'C', 2, 59)\n(2, 'kbdsr', 'female', 18, 'Finance', 'R', 1, 21)\n(2, 'kbdsr', 'female', 18, 'Finance', 'Go', 1, 19)\n(3, 'vtjxo', 'female', 12, 'Economics', 'Python', 3, 38)\n(3, 'vtjxo', 'female', 12, 'Economics', 'Java', 2, 38)\n(3, 'vtjxo', 'female', 12, 'Economics', 'C++', 3, 46)\n(3, 'vtjxo', 'female', 12, 'Economics', 'C', 2, 2)\n(4, 'gnvcs', 'male', 17, 'Economics', 'Python', 3, 29)\n(4, 'gnvcs', 'male', 17, 'Economics', 'Java', 2, 20)\n(4, 'gnvcs', 'male', 17, 'Economics', 'C++', 3, 16)\n(4, 'gnvcs', 'male', 17, 'Economics', 'C', 2, 33)\n(4, 'gnvcs', 'male', 17, 'Economics', 'R', 1, 76)\n(4, 'gnvcs', 'male', 17, 'Economics', 'Go', 1, 83)\n(5, 'wcmvy', 'female', 13, 'Finance', 'Python', 3, 48)\n(5, 'wcmvy', 'female', 13, 'Finance', 'Java', 2, 97)\n(5, 'wcmvy', 'female', 13, 'Finance', 'C++', 3, 46)\n(5, 'wcmvy', 'female', 13, 'Finance', 'C', 2, 14)\n(5, 'wcmvy', 'female', 13, 'Finance', 'R', 1, 77)\n(5, 'wcmvy', 'female', 13, 'Finance', 'Go', 1, 75)\n(6, 'yuhmv', 'female', 12, 'CS', 'Python', 3, 96)\n(6, 'yuhmv', 'female', 12, 'CS', 'Java', 2, 70)\n(6, 'yuhmv', 'female', 12, 'CS', 'C++', 3, 19)\n(7, 'gtoyh', 'female', 24, 'Economics', 'Python', 3, 72)\n(7, 'gtoyh', 'female', 24, 'Economics', 'Java', 2, 76)\n(8, 'trdvb', 'female', 24, 'Math', 'Python', 3, 40)\n(8, 'trdvb', 'female', 24, 'Math', 'Java', 2, 9)\n(8, 'trdvb', 'female', 24, 'Math', 'C++', 3, 92)\n(8, 'trdvb', 'female', 24, 'Math', 'C', 2, 40)\n(9, 'nwqxo', 'male', 13, 'Economics', 'Python', 3, 77)\n(9, 'nwqxo', 'male', 13, 'Economics', 'Java', 2, 96)\n(10, 'ugkqn', 'male', 13, 'CS', 'Python', 3, 29)\n(10, 'ugkqn', 'male', 13, 'CS', 'Java', 2, 74)\n(10, 'ugkqn', 'male', 13, 'CS', 'C++', 3, 4)\n(10, 'ugkqn', 'male', 13, 'CS', 'C', 2, 81)\n(10, 'ugkqn', 'male', 13, 'CS', 'R', 1, 40)\n(10, 'ugkqn', 'male', 13, 'CS', 'Go', 1, 72)\n(11, 'lpcqm', 'male', 13, 'Finance', 'Python', 3, 12)\n(11, 'lpcqm', 'male', 13, 'Finance', 'Java', 2, 94)\n(11, 'lpcqm', 'male', 13, 'Finance', 'C++', 3, 76)\n(11, 'lpcqm', 'male', 13, 'Finance', 'C', 2, 4)\n(12, 'kgxfn', 'male', 24, 'Math', 'Python', 3, 46)\n(12, 'kgxfn', 'male', 24, 'Math', 'Java', 2, 26)\n(13, 'vetjq', 'male', 20, 'Math', 'Python', 3, 26)\n(13, 'vetjq', 'male', 20, 'Math', 'Java', 2, 31)\n(13, 'vetjq', 'male', 20, 'Math', 'C++', 3, 71)\n(13, 'vetjq', 'male', 20, 'Math', 'C', 2, 1)\n(13, 'vetjq', 'male', 20, 'Math', 'R', 1, 25)\n(13, 'vetjq', 'male', 20, 'Math', 'Go', 1, 94)\n(14, 'nfwum', 'male', 19, 'CS', 'Python', 3, 29)\n(14, 'nfwum', 'male', 19, 'CS', 'Java', 2, 1)\n(14, 'nfwum', 'male', 19, 'CS', 'C++', 3, 21)\n(14, 'nfwum', 'male', 19, 'CS', 'C', 2, 75)\n(14, 'nfwum', 'male', 19, 'CS', 'R', 1, 23)\n(15, 'mcbfi', 'female', 15, 'CS', 'Python', 3, 8)\n(15, 'mcbfi', 'female', 15, 'CS', 'Java', 2, 29)\n(16, 'vjskh', 'male', 20, 'Math', 'Python', 3, 100)\n(16, 'vjskh', 'male', 20, 'Math', 'Java', 2, 74)\n(16, 'vjskh', 'male', 20, 'Math', 'C++', 3, 30)\n(16, 'vjskh', 'male', 20, 'Math', 'C', 2, 55)\n(16, 'vjskh', 'male', 20, 'Math', 'R', 1, 43)\n(16, 'vjskh', 'male', 20, 'Math', 'Go', 1, 83)\n(17, 'dzjra', 'female', 16, 'Economics', 'Python', 3, 71)\n(17, 'dzjra', 'female', 16, 'Economics', 'Java', 2, 98)\n(17, 'dzjra', 'female', 16, 'Economics', 'C++', 3, 55)\n(18, 'qdvxt', 'male', 24, 'CS', 'Python', 3, 69)\n(18, 'qdvxt', 'male', 24, 'CS', 'Java', 2, 38)\n(18, 'qdvxt', 'male', 24, 'CS', 'C++', 3, 67)\n(18, 'qdvxt', 'male', 24, 'CS', 'C', 2, 29)\n(18, 'qdvxt', 'male', 24, 'CS', 'R', 1, 23)\n(18, 'qdvxt', 'male', 24, 'CS', 'Go', 1, 96)\n(19, 'hnyie', 'female', 15, 'Finance', 'Python', 3, 69)\n(19, 'hnyie', 'female', 15, 'Finance', 'Java', 2, 57)\n(19, 'hnyie', 'female', 15, 'Finance', 'C++', 3, 41)\n(19, 'hnyie', 'female', 15, 'Finance', 'C', 2, 90)\n(20, 'iaoxv', 'male', 14, 'CS', 'Python', 3, 16)\n(20, 'iaoxv', 'male', 14, 'CS', 'Java', 2, 82)\n(20, 'iaoxv', 'male', 14, 'CS', 'C++', 3, 77)\n(20, 'iaoxv', 'male', 14, 'CS', 'C', 2, 63)\n"
     ]
    }
   ],
   "source": [
    "# 查询学生的个人信息及分数\n",
    "cursor.execute('''select id,name,gender,age,major,class_name,credit,score\n",
    "                  from students s\n",
    "                  inner join\n",
    "                  (select student_id,class_name,c.credit,score\n",
    "                   from class c\n",
    "                   inner join enrolled e\n",
    "                   on c.class_id = e.class_id) tc\n",
    "                  on s.id =tc.student_id''')\n",
    "print('---------------inner join--------------------')\n",
    "for x in cursor.fetchall():\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "创建视图备练习5使用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#创建视图以便查询\n",
    "cursor.execute('create view score as \\\n",
    "    select id,name,class_name,credit,score            \\\n",
    "    from students s inner join\\\n",
    "    (select student_id,class_name,c.credit,score from class c inner join enrolled e on c.class_id = e.class_id)tc \\\n",
    "    on s.id =tc.student_id')\n",
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 关闭Cursor:\n",
    "cursor.close()\n",
    "# 关闭connection：\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 练习5\n",
    "结合Tkinter，做一个成绩查询的SQLite系统 \\\n",
    "提示：  \\\n",
    "创建一个查询按钮，查询所有students表\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## test_tkinter.py\n",
    "   简单的成绩查询界面"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " where id=1\n",
      " where id=1\n",
      " where id=2\n",
      " where id=2 and name=\"Python\"\n",
      " where id=2 and class_name=\"Python\"\n"
     ]
    }
   ],
   "source": [
    "from tkinter import *\n",
    "from tkinter import ttk\n",
    "from tkinter import messagebox\n",
    "import sqlite3\n",
    "\n",
    "def query():\n",
    "    id=in_id.get()\n",
    "    name=in_name.get()\n",
    "    course=in_co.get()\n",
    "    #清空treeview\n",
    "    items=tree.get_children()\n",
    "    for item in items:\n",
    "        tree.delete(item)\n",
    "    #查询条件\n",
    "    q_id = ''if id=='' else 'id='+id\n",
    "    q_name = '' if name=='' else 'name=\"{}\"'.format(name)\n",
    "    q1 = ' and ' if (q_id!='' and q_name!='') else ''\n",
    "    q_course = '' if course=='' else 'class_name=\"{}\"'.format(course)\n",
    "    q2 = ' and ' if ((q_id+q_name)!='' and q_course!='') else ''\n",
    "    q = q_id+q1+q_name+q2+q_course\n",
    "    q = q if q=='' else ' where '+q\n",
    "    print(q)\n",
    "    #执行查询,插入结果到表格\n",
    "    cursor.execute('select * from score'+q)\n",
    "    #cursor.execute('select * from students'+q)\n",
    "    count=0  \n",
    "    for item in cursor.fetchall():\n",
    "        tree.insert('','end',values=item)\n",
    "        count+=1\n",
    "    if count==0:\n",
    "        messagebox.showinfo('提醒','未查询到相关结果')\n",
    "\n",
    "# 数据库文件是university.db，不存在，则自动创建\n",
    "with sqlite3.connect('university.db') as db:\n",
    "# 创建一个cursor：\n",
    "    cursor = db.cursor()\n",
    "\n",
    "# 创建主窗口对象\n",
    "root = Tk()\n",
    "# 设置初始化界面大小\n",
    "root.geometry('600x400')\n",
    "root.resizable(False, False)\n",
    "# 窗口标题\n",
    "root.title(\"成绩查询系统\")\n",
    "\n",
    "frame1 = Frame(root)\n",
    "frame2 = Frame(root)\n",
    " \n",
    "frame1.pack(side='top',ipady=10)\n",
    "frame2.pack()\n",
    "\n",
    "lb_id=Label(frame1,text='学号').pack(side='left')\n",
    "in_id=Entry(frame1,width=10)\n",
    "in_id.pack(side='left',padx=10)\n",
    "lb_name=Label(frame1,text='姓名').pack(side='left')\n",
    "in_name=Entry(frame1,width=10)\n",
    "in_name.pack(side='left',padx=10)\n",
    "lb_co=Label(frame1,text='课程').pack(side='left')\n",
    "in_co=Entry(frame1,width=10)\n",
    "in_co.pack(side='left',padx=10)\n",
    "\n",
    "bt_query = Button(frame1,text = '查询',command = query)\n",
    "bt_query.pack(side='right',padx=10)\n",
    "\n",
    "tree=ttk.Treeview(frame2,show=\"headings\")   #表格\n",
    "tree['columns']=('学号','姓名','课程','学分','成绩')\n",
    "tree.column('学号',width=100,anchor='center')   #表示列,不显示\n",
    "tree.column('姓名',width=80,anchor='center')\n",
    "tree.column('课程',width=100,anchor='center')\n",
    "tree.column('学分',width=50,anchor='center') \n",
    "tree.column('成绩',width=80,anchor='center')\n",
    "\n",
    "tree.heading('学号',text='学号')  #显示表头\n",
    "tree.heading('姓名',text='姓名')\n",
    "tree.heading('课程',text='课程')\n",
    "tree.heading('学分',text='学分')\n",
    "tree.heading('成绩',text='成绩')\n",
    "\n",
    "#滚动条\n",
    "vbar = ttk.Scrollbar(frame2,orient=VERTICAL,command=tree.yview)\n",
    "tree.configure(yscrollcommand=vbar.set)\n",
    "vbar.pack(side='right',fill='y')\n",
    "\n",
    "hbar = ttk.Scrollbar(frame2,orient=HORIZONTAL,command=tree.xview)\n",
    "tree.configure(xscrollcommand=hbar.set)\n",
    "hbar.pack(side='bottom',fill='x')\n",
    "tree.pack()\n",
    "\n",
    "#关闭窗口时断开数据库\n",
    "def on_closing():\n",
    "    # 关闭Cursor:\n",
    "    cursor.close()\n",
    "    # 关闭connection：\n",
    "    db.close() \n",
    "    root.destroy()\n",
    "\n",
    "root.protocol(\"WM_DELETE_WINDOW\", on_closing)\n",
    "\n",
    "# 加入消息循环\n",
    "root.mainloop()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5-final"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}